from datetime import datetime
from tools.mysqlconntool import mysqlconn

def item_time(sql_query):
    connection = mysqlconn.get_connection()
    cursor = connection.cursor()
    role = ('user',)
    sql = f'''
                SELECT
                	a.NAME,
                	a.admission_no,
                	a.phone,
                	sum( a.train_time_sum ) AS train_time_sum,
                	a.rating_date AS days 
                FROM
                	(
                SELECT
                	u.NAME,
                	u.id,
                	r.video_name,
                	u.admission_no,
                	u.phone,
                	IFNULL( r.train_time_sum, 0 ) AS train_time_sum,
                	d.rating_date 
                FROM
                	users u
                	CROSS JOIN ( 
                	{sql_query}
                	) d
                	LEFT JOIN ratings r ON u.id = r.user_id 
                	AND DATE( r.create_time ) = d.rating_date 
                WHERE
                	u.roles = %s
                	AND u.group_class = 1 
                ORDER BY
                	d.rating_date,
                	u.id 
                	) a 
                GROUP BY
                	a.rating_date,
                	a.id
            '''
    print(sql)
    cursor.execute(sql, role)
    result = cursor.fetchall()
    return result

def get_original_date():
    connection = mysqlconn.get_connection()
    cursor = connection.cursor()
    sql = f'''
            SELECT MIN(DATE(create_time)) AS earliest_date FROM ratings
          '''
    cursor.execute(sql)
    result = cursor.fetchall()
    new_result = data_time(result)
    # 定义日期格式
    date_format = "%Y-%m-%d %H:%M:%S"
    # 转换字符串为datetime对象
    date_object = datetime.strptime(new_result, date_format)
    return date_object

def data_time(date_tuple):

    # 获取日期字符串并转换为 datetime 对象
    date_str = date_tuple[0]
    date_obj = datetime.strptime(date_str[0], '%Y-%m-%d')

    # 转换为目标格式
    formatted_date = date_obj.strftime('%Y-%m-%d 00:00:00')
    return formatted_date

def time_month_sum():
    connection = mysqlconn.get_connection()
    cursor = connection.cursor()
    sql = f'''
        SELECT
        user_id, 
        name,
        admission_no,
        DATE_FORMAT(create_time, '%Y-%m') AS month,
        SUM(train_time_sum) AS month_run_time_sum
        FROM 
        ratings
        GROUP BY 
        name, admission_no, DATE_FORMAT(create_time, '%Y-%m')
        ORDER BY 
        name, month;
    '''
    print(sql)
    cursor.execute(sql)
    result = cursor.fetchall()
    return result

if __name__ == '__main__':
    ret = get_original_date()
    print(ret)